﻿using IShoppingBll;
using IShoppingDal;
using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using ShoppingEntity;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ShoppingBll
{
    public class CommodityRecordBll : BaseBll<Commodity_Record>, ICommodityRecordBll
    {
        private ICommodity_RecordDal _commodityRecordDal;
        private IUserInfoDal _userInfoDal;
        private ICommodityInfoDal _commodityInfoDal;
        private ShoppingDbContext _dbContext;
        public CommodityRecordBll(ICommodity_RecordDal commodityRecordDal, IUserInfoDal userInfoDal, ICommodityInfoDal commodityInfoDal, ShoppingDbContext dbContext) : base(commodityRecordDal)
        {
            _commodityRecordDal = commodityRecordDal;
            _userInfoDal = userInfoDal;
            _commodityInfoDal = commodityInfoDal;
            _dbContext = dbContext;
        }

        /// <summary>
        /// 添加商品
        /// </summary>
        /// <param name="commodityRecord"></param>
        /// <returns></returns>
        public bool AddCommodityRecord(Commodity_Record commodityRecord)
        {
            //return _commodityRecordDal.Add(commodityRecord);
            using (var transaction = _dbContext.Database.BeginTransaction())
            {

                //获取商品
                var commodityInfo = _commodityInfoDal.GetEntity(commodityRecord.GoodsId);
                if (commodityInfo != null && commodityRecord != null)
                {
                    //添加商品
                    bool isSuccess = _commodityRecordDal.Add(commodityRecord);
                    //修改商品数量
                    commodityInfo.Num += commodityRecord.Num;
                    bool isSuccess2 = _commodityInfoDal.Update(commodityInfo);
                    if (isSuccess && isSuccess2)
                    {
                        transaction.Commit();
                        return true;
                    }
                    else
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
                else
                {
                    transaction.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="page"></param>
        /// <param name="limit"></param>
        /// <param name="count"></param>
        /// <param name="goodsId"></param>
        /// <returns></returns>
        public object GetCommodityRecordListByPage(int page, int limit, out int count, string goodsId)
        {
            //获取商品信息集合
            var commodityRecords = _commodityRecordDal.GetEntitiesDb().AsQueryable();

            if (!string.IsNullOrEmpty(goodsId))
            {
                commodityRecords = commodityRecords.Where(u => u.GoodsId == goodsId);
            }
            //查询出来数据的数量
            count = commodityRecords.Count();
            //连表查询
            var tempList = (from cr in commodityRecords
                            join c in _commodityInfoDal.GetEntitiesDb().Where(c => !c.IsDelete)
                            on cr.GoodsId equals c.ID into tempCrC
                            from cc in tempCrC.DefaultIfEmpty()

                            join u in _userInfoDal.GetEntitiesDb().Where(u => !u.IsDelete)
                            on cr.Creator equals u.ID into tempUrC
                            from uu in tempUrC.DefaultIfEmpty()
                            select new
                            {
                                cr.ID,
                                cr.CreateTime,
                                cr.Num,
                                //cr.Type,
                                goodsName = cc.CommodityName,
                                creator = uu.UserName
                            });
          
            //分页
            tempList = tempList.OrderBy(u => u.CreateTime).Skip((page - 1) * limit).Take(limit);
            var list = tempList.ToList().Select(u => new
            {
                u.creator,
                u.goodsName,
                u.Num,
                //Type = u.Type == 1 ? "入库" : "出库",
                u.ID,
                CreateTime = u.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")
            });
            return list;
        }





        /// <summary>
        /// 商品文件上传
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="iD"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Upload(Stream stream, string userInfoId, out string msg ,bool geshisss)
        {
            IWorkbook Workbook;

            //文件格式：NPOI 使用 HSSFWorkbook 类来处理 xls，XSSFWorkbook 类来处理 xlsx，
            //它们都继承接口 IWorkbook，因此可以通过 IWorkbook 来统一处理 xls 和 xlsx 格式的文件

            msg = "";
            stream.Position = 0;
            if (geshisss)
            {
                Workbook = new XSSFWorkbook(stream);
            }
            else
            {
                Workbook = new HSSFWorkbook(stream);
            }



            Workbook.GetSheetAt(0);
            ISheet sheet = Workbook.GetSheetAt(0);
            if (sheet != null)
            {
                using (var transaction = _dbContext.Database.BeginTransaction())
                {

                    var rowLength = sheet.LastRowNum;
                    //添加商品记录集合
                    List<Commodity_Record> commodityRecords = new List<Commodity_Record>();
                    //批量修改商品记录集合
                    List<CommodityInfo> commodityInfos = new List<CommodityInfo>();

                    for (int i = 1; i <= rowLength; i++)
                    {
                        //获取第一行
                        IRow row = sheet.GetRow(i);

                        if (row != null)
                        {
                            //列
                            int cellCount = row.LastCellNum;
                            var commodityName = row.Cells[0].ToString();
                            var value = row.Cells[1].ToString();

                            //类型转换
                            int num = 0;
                            int.TryParse(value, out num);

                            //根据名称查询商品ID
                            var commodityInfo = _commodityInfoDal.GetEntitiesDb().FirstOrDefault(c => c.CommodityName == commodityName);
                            if (commodityInfo != null)
                            {
                                Commodity_Record consumableRecord = new Commodity_Record()
                                {
                                    ID = Guid.NewGuid().ToString(),
                                    CreateTime = DateTime.Now,
                                    //Type = 1,
                                    Creator = userInfoId,
                                    Num = num,
                                    GoodsId = commodityInfo != null ? commodityInfo.ID : ""
                                };


                                commodityRecords.Add(consumableRecord);

                                //添加商品数量
                                commodityInfo.Num += num;
                                commodityInfos.Add(commodityInfo);


                            }
                            else
                            {
                                msg = string.Format("{0}商品有误,在第{1}行,进货列表中没有该商品", commodityName, i + 1);
                                transaction.Rollback();
                                if (string.IsNullOrEmpty(msg))
                                {
                                    msg = "数据有误";
                                }
                                return false;
                            }


                        }


                    }
                    int index = _commodityRecordDal.Adds(commodityRecords);

                    _commodityInfoDal.Updates(commodityInfos);
                    transaction.Commit();
                    return true;

                }

            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 商品文件下载
        /// </summary>
        /// <returns></returns>
        public Stream GetDownloadDatas()
        {
            var datas = (from cr in _commodityRecordDal.GetEntitiesDb()
                         join c in _commodityInfoDal.GetEntitiesDb().Where(c => !c.IsDelete)
                         on cr.GoodsId equals c.ID
                         select new
                         {
                             cr.Num,
                             c.CommodityName,
                             cr.CreateTime,
                             //cr.Type
                         }).ToList();
            //获取当前目录
            var dPath = Directory.GetCurrentDirectory();
            //文件名
            var fileName = "output.xlsx";
            var filePath = Path.Combine(dPath, fileName);
            //数据流
            FileStream fileStream = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            //文件管理对象
            IWorkbook workbook = new XSSFWorkbook();
            //创建第一个sheet表格
            ISheet excelSheet = workbook.CreateSheet("Sheet1");

            for (int i = 0; i < datas.Count; i++)
            {
                //创建一行
                IRow row = excelSheet.CreateRow(i);
                //创建一列
                row.CreateCell(0).SetCellValue(datas[i].CommodityName);
                //创建二列
                row.CreateCell(1).SetCellValue(datas[i].Num);
                //创建三列
                //string typeStr = datas[i].Type == 1 ? "入库" : "出库";
                //row.CreateCell(2).SetCellValue(typeStr);
                //创建四列
                string dataTimeStr = datas[i].CreateTime.ToString("yyyy-mm-dd HH:mm:ss");
                row.CreateCell(2).SetCellValue(dataTimeStr);
            }
            //写入文件流
            workbook.Write(fileStream);
            //读取已写文件
            FileStream resStream = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            return resStream;
        }

    }
}
